In many implementations, a sequence is a schema object that can generate unique sequential values. Sequence objects are commonly used by database applications to generate primary and unique keys while loading or scanning data tables, or to generate timestamps, for example. Sequence objects encapsulate a method for generating the next value of a sequence. For example, a primitive NEXTVAL is used in database applications from Oracle Corporation. Using Oracle syntax, a NEXTVAL operation on sequence SEQ is referred to as SEQ.NEXTVAL.
The NEXTVAL operation on a sequence generates the next unique value in an ascending or descending order, depending on the sequence definition. For example, a sequence can be created and implemented using the following SQL statements:                CREATE SEQUENCE SEQ START WITH 0 INCREMENT 1;        SELECT c1, c2, SEQ.NEXTVAL FROM TAB.The first line creates a sequence called SEQ, which starts with the number zero as the first value of the sequence and increments each successive value of the sequence by one. The second line applies the NEXTVAL operation of sequence object SEQ to each row fetched from table TAB. That is, it attaches a new next value, incremented by one from the previous value, to each row consisting of c1 and c2 from table TAB.        
Significantly, a sequence object is globally accessible by several sessions, and thus several users, in a database instance. Hence, to ensure the uniqueness of each sequence value generated for each session call to the sequence object, NEXTVAL operations on the same sequence object are serialized across all processes using a synchronization primitive, for example, a lock.
In a common data-warehousing scenario, multiple processes accessing the same sequence object can be a coordinator process or slave processes spawned by the coordinator. Generally, a parallel query is executed by a coordinator process which spawns the slave processes. For example, the following SQL statement spawns 100 slave processes, which scan the table TAB in parallel, with each process requesting generation of a unique sequence value from the shared sequence object SEQ for each row scanned from table TAB:                SELECT/*+parallel(TAB 100)*/c1, c2, SEQ.NEXTVAL FROM TAB.        
The slave processes spawned by the preceding SQL statement synchronize, with respect to the NEXTVAL operation of the sequence object SEQ, using a database instance level lock (referred to further as a K lock). Thus, access to the sequence SEQ is protected and non-trivial contention is thereby encountered. In other words, in accessing the sequence object for requesting a unique sequence value from the sequence, a requesting slave process executing in a given database instance might have to wait for another slave process executing in the same instance, which presently has control of the K lock. Therefore, the more parallelism that is introduced into a query execution, the more contention is introduced for the instance level lock and, consequently, the higher the miss ratio for the lock.
For the foregoing reasons, parallel query execution that requires making parallel calls to a sequence NEXTVAL operation, or a similarly functioning operation, can be highly unscalable. Indeed, the time to complete a given SQL statement that contains a sequence NEXTVAL operation can increase with increasing degrees of parallelism.
In addition, processes from different database instances of the same database also contend for a sequence object when performing NEXTVAL operations. These processes typically synchronize on a cluster level lock (referred to further as an R lock). Hence, a process that is attempting to obtain the next sequence value will first encounter the local instance level K lock and then the cluster level R lock for the sequence object, before gaining access to the sequence object to obtain the next sequence value for that respective instance. In an Oracle database implementation, caching sequence values or numbers inside shared memory associated with a database instance reduces the cross-instance contention described above, by creating a sequence object with a CACHE clause.
Based on the foregoing, it is clearly desirable to provide an improved mechanism for parallel processing in relation to sequence operations.